How To Install Linux, Apache, MySQL, PHP (LAMP) stack on Ubuntu 18.04

LAMP
Linux operating system
Apache web server
MySQL database
dynamic content is processed by PHP

之前安裝了Linux operating system和Apache web server，所以接下來安裝MySQL database

Step 2 — Installing MySQL

sudo apt install mysql-server(安裝mysql)
sudo mysql_secure_installation(mysql安全設定)

如圖:有3種強度的密碼

一連串的安全設定，選y就好

sudo mysql(進入資料庫模式)

SELECT user,authentication_string,plugin,host FROM mysql.user;

plugin代表密碼方式有:auth_socket和mysql_native_password
接著把root的auth_socket換成mysql_native_password

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

'password'就是密碼
這邊就錯誤了，因為我完全照抄
'password'這個密碼強度太弱

接著再更改密碼就好了:

更改完後:

FLUSH PRIVILEGES;(reload the grant tables and put your new changes into effect)
SELECT user,authentication_string,plugin,host FROM mysql.user;(查看有沒有改變密碼方式)
exit(離開)

但不能用這個指令登到mysql

https://www.youtube.com/watch?v=MJmEHUa5K38

要用這個指令使用mysql:

mysql -u root –p(接著打mysql密碼就可以登mysql)

可是如果忘記密碼怎麼辦?
先看一些關於mysql狀態的指令:
https://www.ubuntu-tw.org/modules/newbb/viewtopic.php?viewmode=compact&order=ASC&topic_id=106948&forum=7

有3種類似的東西: /etc/init.d/mysql、systemctl、service

如果有安裝mysql-server的話，執行這個指令sudo /etc/init.d/mysql
就會有Usage: /etc/init.d/mysql start|stop|restart|reload|force-reload|status

執行:

sudo /etc/init.d/mysql status

就可以看到mysql 的狀態

ps aux | grep mysql

資料庫狀況、重啟、停止、開始

/etc/init.d/mysql:

restart-->sudo /etc/init.d/mysql restart
stop-->sudo /etc/init.d/mysql stop
start-->sudo /etc/init.d/mysql start

systemctl:

sudo systemctl status mysql
sudo systemctl restart mysql
sudo systemctl stop mysql
sudo systemctl start mysql

service:

sudo service mysql status
sudo service mysql restart
sudo service mysql stop
sudo service mysql start

接著回到要怎麼改密碼
Can't reset root password with --skip-grant-tables on ubuntu 16

1 sudo service mysql start(可能要等一段時間)
2 cd /var/run
3 sudo cp -rp ./mysqld ./mysqld.bak
4 sudo service mysql stop(可能要等一段時間)
5 sudo mv ./mysqld.bak ./mysqld
6 sudo mysqld_safe --skip-grant-tables &(要換一個畫面，putty-->右鍵-->Duplicate Session)
7 mysql -u root
8 SET PASSWORD FOR root@'localhost' = PASSWORD('my_new_password');
或ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

會出現這個錯誤:
https://unix.stackexchange.com/questions/58655/turn-off-skip-grant-tables-in-mysql

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

所以要先執行

FLUSH PRIVILEGES;

照著就可以改密碼了，但改完又出現錯誤

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

解決方法:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
這個指令就可以登mysql了

mysql -h 127.0.0.1 -P 3306 -u root –p

看一下mysql的資料表

use mysql;
show tables;

會發現有個 user表格，改密碼就是在這個表格:

倒著順序查看user表格的欄位:

desc user;

沒有Password這個欄位，所以這個方法不能:

update mysql.user set Password='newpassword' where User='root'

MySQL 5.7 authentication_string欄位取代Password欄位:
https://stackoverflow.com/questions/30692812/mysql-user-db-does-not-have-password-columns-installing-mysql-on-osx

所以可以看下欄位有什麼:

SELECT user,authentication_string,plugin,host,password_last_changed FROM mysql.user;

要退出的時候習慣按ctrl+z
常常會有[1]+ Stopped。
解決方法:
How can I resume a stopped job in Linux?

很多指令和資料庫語法都還不了解
相關連結:
java，mysql、jdbc

資料庫語法那些可以到leetcode去練習題目